UI enabling mapping engine system and process interconnecting spreadsheets and database-driven applications

ABSTRACT

The present invention relates to a new method to create a new User Interface to Database Data. A Spreadsheet is used as a UI, and the Data presented to the End User is into the Spreadsheet in a repeatable manner A Record serves as the basis of the Data to be presented and the Lists from the List Views are mapped into Managed Regions in the Spreadsheet as in the combination of the Form View and List View. This provides a new means to create Applications. An existing Spreadsheet can be used to create a new Application. The Spreadsheet mappings are created. These mappings are then used to create the Tables in the Database to store the required data and relationships. The End User UI is now complete, because the Spreadsheet is now the UI for the End User using the Mapping Engine to Process User Action.

TECHNICAL FIELD

Embodiments of the present invention are directed to mapping enginesenabling spreadsheets to be user interfaces for database data anddatabase-driven applications.

BACKGROUND ART

Applications and databases provide user interfaces to their data inseveral different methodologies. One method is to list data from a tableand present it into a simple table or show the results of one record asa form for the end user to view and potentially modify. This has workedwell over the years when the data is simple, however, applications anddata have become more complex with multiple tables of data needed toprovide the appropriate information to the end user. From this, anothermethod arose. This is to show the “Form” from a single record and anyrelated information in tables on the form. This enables the end user touse data on the single record and all related information from othertables on a single form. For example, see FIG. 1 expense Table 100, anexample of a database table for expense reports. It contains all theneeded data for an Expense application, however more information fromother tables is necessary. See FIG. 2 Day Expense Table 200, a tablecontaining the daily expenses for the expense. Records with ID's of 3-6are related to Expense Table record with ID of 1. The application canuse these records to calculate the total expenses that is in ExpenseTable record ID of 1 total field. In addition, see FIG. 3 Expense MaxTable 300, a table containing the maximum amounts that can be chargedper department. Using the department fields in these records and theExpense Table 100 record, the application can ensure that thedepartmental maximums are not exceeded. This is an example of multipletables, having diverse relationships, that are needed by an application.This data is presented to the user in an Application interface such asFIG. 4 Application Expense Report 400, an example using the data forExpense Table 100 with ID 1 in an Application window.

While this has been a success, there are several limitations to thisapproach:

1) It requires the end user to learn the form.

2) Adding, deleting, and updating related data can become confusing,error-prone, and difficult for the end user. It may require multiplescreen/form changes to perform these actions.

3) Data calculations presented on the form, such as summing the costfield of a related table, may only appear on the form and not in thedatabase. Thus, they can be impossible or difficult to change. Manytimes, advanced calculations are coded into the application.

To resolve these issues, spreadsheets may be utilized. A spreadsheet isa computer application having a collection of data entered in rows andcolumns. Each collection of data is commonly referred to as a worksheet.Each row is commonly referred to by an integer number starting at 1 onthe first row and increasing by 1 for each additional row in thespreadsheet. Each column is commonly referred to by a letter of thealphabet. Each digit in the column name uses a letter A-Z to represent anumber of 0-25 in a numeric base of 26. This means column #1 is A,column #26 is Z, column #27 is AA and so on. With these references, asingle cell can be referenced by its column and row; thus A1 is thefirst cell in a spreadsheet as it is column 1 row 1. A set of cells canbe referenced in what is commonly known as a range by specifying a topleft cell:bottom right cell.

Cells can contain different types of data such as numbers, currency,dates, text, or formulas. Formulas can reference cells and ranges intheir computations and display to the user the results of thecomputations.

A spreadsheet can be made up of multiple worksheets, each of which canhave different data. Cells and ranges from one worksheet can bereferenced on different worksheets within the same spreadsheet.

To resolve the Application issues, Applications export to spreadsheetformats. This allows an interface that many users already know, andwhich is flexible in its calculation model. This makes the data from thedatabase or database application more flexible for the end user andeasier to use. However, changes to the data are not reflected into thedatabase. This makes this type of methodology useful for reporting oranalysis.

This can be seen from such patents as U.S. Pat. No. 8,082,489, “Using aSpreadsheet Engine as a Server-Side Calculation Model” and U.S. Pat. No.6,631,497, “Binding Data from Data Source to Cells in a Spreadsheet.”These patents acknowledge spreadsheets as a better interface to the enduser, and attempt to use them for displaying the data or using thecalculation model. U.S. Pat. No. 8,082,489 saves the calculation datamodel on an application server and enables a more flexible calculationmodel, but it still involves the traditional approach to the end userinterface. U.S. Pat. No. 6,631,497 uses a spreadsheet for the end userinterface for data from a database. It requires the user to select thedata, relationships, and fields to bind on the spreadsheet. While thedata does affect the calculation model, unlike U.S. Pat. No. 8,082,489,there is no means to synchronize these results back to the database.This method is an interface to data from a database but could not beused as an end user interface to an application. It is better describedas an interface to a report of data, but doesn't allow for it to be astandard application user interface.

An example of the use of a spreadsheet from a database ordatabase-driven application is seen from the previous example of data ona form in an application seen in FIG. 4—Application Expense Report and aspreadsheet with the same data in FIG. 5, Expense Report Spreadsheet500. The information/data on the application form is displayed in aspreadsheet. The user can then use the spreadsheet functionality toanalyze, print, and change the data. However, any changes are “lost” asthe data is not synchronized back to the database.

SUMMARY

Embodiments of the present invention may provide an algorithmic mappingengine between a database and/or database-driven application andcell(s)/range(s) in a spreadsheet to enable the use of the spreadsheetas a user interface to the data/application.

In one embodiment of the present invention, a mapping engine is providedwhich provides the mapping capabilities that enable:

-   -   Initial generation of a spreadsheet from existing data using a        base record    -   Ability for the user to make changes in the spreadsheet and have        the corresponding data in the database/application reflect the        changes    -   Ability for the user to make changes in the spreadsheet which        are not mapped, yet may be used in the spreadsheet calculation        engine to derive results that may be mapped    -   Ability to leverage the spreadsheet calculation engine to derive        results which are mapped back to the database/application    -   Ability to handle a dynamic number of records from a query that        are mapped into a range in a spreadsheet, and handle when there        are a smaller or greater number of records than the specified        range can contain

In one embodiment, there is provided a method for using a spreadsheet asa user interface to a database operably connected to a computer system,the computer system having one or more processors and storage, themethod carried out through execution of program instructions executed bythe one or more processors. The method may include steps and processesto retrieve, manipulate, and store data in a plurality of datastructures including databases and spreadsheets. The data structures mayalso include cell maps, a cell map being adapted to bidirectionalmapping or translating of data values inputted and displayed via aspreadsheet to data values stored and retrieved from a database. Thedata structures may also include column maps, a column map being a datastructure associating a database table field with a spreadsheet rangecolumn and optionally having one or more cell maps. The data structuresmay also include table maps, a table map being a data structureassociating a query on a database table with a spreadsheet range andoptionally having one or more column maps. The data structures may alsoinclude field maps, a field map being a data structure associating adatabase table field with a spreadsheet cell and optionally having oneor more cell maps. The data structures may also include record maps, arecord map being a data structure identifying a database table andhaving a query on the database table, and optionally having one or morefield maps. The data structures may also include a template, thetemplate which may have a spreadsheet, table maps, and record maps ofwhich one is a base record map.

The method may include instantiating a mapping engine by retrieving atemplate from storage or the database and iterating, over the template'stable maps and record maps beginning with the base record map, thefollowing steps:

If the iteration is for a record map: using the query associated withthe iterated record map to retrieve a record from the database anditerating over each field map associated with the iterated record map toupdate the spreadsheet cell associated with the iterated field map withthe data from the retrieved record stored at the field associated withthe iterated field map, applying the mapping or translation designatedin the cell map associated with the iterated field map, if any.

If the iteration is for a table map: using the query associated with theiterated table map to retrieve one or more records from the database anditerating over each retrieved record as follows: iterating over eachcolumn map associated with the iterated table map to update aspreadsheet cell at R:C in the range associated with the iterated tablemap with the data from the iterated retrieved record stored at the fieldassociated with the iterated column map, applying the mapping ortranslation designated in the cell map associated with the iteratedcolumn map, if any, where C is the column associated with the iteratedcolumn map and R is the ordinal number of the current retrieved recorditeration for the iterated table.

Instantiating the mapping engine may also include installing calls tothe mapping engine into the spreadsheet, whereby when the user attemptsan action on the spreadsheet, the mapping engine is called.

The method may include presenting the spreadsheet to the user andinvoking the mapping engine when the user takes an action affecting acell in the spreadsheet. The mapping engine may determine whether thecell is associated with any table map or record map in the template and,if not, then permitting the action. The mapping engine may determinewhether the cell is in a range associated with a table map in thetemplate and the action is to change the value of the cell and, if so,then: permitting the action affecting the cell, determining whether arecord for the cell exists in the table associated by the associatedtable map and inserting a record if the record does not exist, andstoring the cell value resulting after the action in the database afterfirst applying the mapping or translating designated in a cell mapassociated with the cell. The mapping engine may determine whether thecell is in a range associated with a table map in the template and theaction is to add a row and, if so, adding a new row by: making a copy ofthe spreadsheet row containing the last row in the associated range,causing the spreadsheet to insert a new row above the last row in theassociated range, iterating over each cell in the new row by copying, ifthe iterated cell is not in a table map or record map, any format andformula of the cell in the same column in the copy of the spreadsheetrow to the iterated cell, iterating over each field map associated withone or more cells in the new row, updating the iterated field map withthe new cell location, and iterating over each table map associated withone or more cells in the new row, copying any format from the samecolumn in the copy of the spreadsheet row correspondingly to each of theone or more cells, and if necessary, updating the iterated table mapwith new row size information.

The mapping engine may determine whether the cell is in a rangeassociated with a table map in the template and the action is to deletea row and, if so, then deleting the corresponding record from thedatabase and refreshing the associated table map to reflect thedeletions. The mapping engine may determine whether the cell isassociated with any table map or record map in the template and not in arange associated with a table map in the template and, if so, thenpermitting the action affecting the cell and storing the cell valueresulting after the action in the database after first applying themapping or translating designated in a cell map associated with thecell.

The method may also include steps of determining whether a cell isprotected when the user takes an action affecting a cell in thespreadsheet, and denying the action if the cell is determined to beprotected. In some embodiments the field map and column map datastructures each further comprise protection indication flags and if thecell is associated with a field map or column map for which theprotection indication flag indicates that the cell is protected, thenthe action is denied. The action may also be denied if it is determinedthat the database does not permit the user to make changes to thedatabase on a database level or field level or record level

In some embodiments, using a query associated with a record map or tablemap includes dynamically modifying the query with one or more valuesobtained via a reference. The one or more values obtained via areference may include one or more values obtained using a base recordreference, a record map reference, or a reference to a spreadsheet cell.

BRIEF DESCRIPTION OF DRAWINGS

For a comprehensive exposure of the features, nature and advantageouseffects of the present invention, reference is now made to the detaileddescription in conjunction with the associated drawings, in which:

FIG. 1 is an exemplary illustration of an Expense Table, includingexemplary expense data.

FIG. 2 is an exemplary illustration of a Day Expense Table, includingexemplary day expense data as may be used in a database ordatabase-driven application for some daily expenses associated with anexpense report. They are linked to the report by the field Expense IDwhich matches the ID in Expense Table 100 (FIG. 1)

FIG. 3 is an exemplary illustration of an Expense Max Table, includingexemplary expense max data.

FIG. 4 is an exemplary illustration of an Expense Application Form, withexemplary expense data, showing what an expense form may look like in anapplication with the data from Expense Table 100, Day Expense Table 200,and Expense Max Table 300.

FIG. 5 is an exemplary illustration of an Expense Report Spreadsheetwith exemplary expense data. It shows what a spreadsheet for an expensereport may look like using the data from Expense Table 100, Day ExpenseTable 200, and Expense Max Table 300.

FIG. 6 is an exemplary illustration of a Mapping Engine process inaccordance with embodiments of the invention, illustrating overall usageof the Mapping Engine and how it is used to enable a spreadsheet to be auser interface for the data in a database or database-drivenapplication.

FIG. 7 is an exemplary illustration of the Expense Report SpreadsheetTable Mappings, a spreadsheet highlighting the ranges in the ExpenseReport Spreadsheet which are a Table Map.

FIG. 8 is an exemplary illustration of the Expense Report SpreadsheetRecord Mappings, a spreadsheet highlighting the cells in the ExpenseReport Spreadsheet which are from a Record Map.

FIG. 9 is an exemplary illustration of the Mapping Types, illustratingcontents and relationships of the two main Mapping Types (Table andRecord) along with the contained Field Map(s) and Column Map(s).

FIG. 10 is an exemplary illustration of a Template, illustratingcontents of a Template which is used by the Mapping Engine.

FIG. 11 is an exemplary illustration of the Process Template processused by the Mapping Engine to process a template.

FIG. 12 is an exemplary illustration of the Process Map process, used bythe Mapping Engine to process a Map definition from the Template.

FIG. 13 is an exemplary illustration of the Perform Record Mappingprocess, illustrating a process the Mapping Engine uses to perform aRecord Mapping.

FIG. 14 is an exemplary illustration of the Perform Table Mappingprocess, illustrating a process the Mapping Engine uses to perform aTable Mapping.

FIG. 15 is an exemplary illustration of the Add New Row to Table Mapprocess, a Process diagram showing how the Mapping Engine adds a new rowto a Table Map.

FIG. 16 is an exemplary illustration of the Process User Action process,a Process diagram showing how the Mapping Engine handles an action fromthe end user.

FIG. 17 is an exemplary illustration of the Process Table Actionprocess, a Process diagram showing how the Mapping Engine handles an enduser action affecting a Table Map.

FIG. 18 is an exemplary illustration of the Process Cell Action process,a Process diagram showing how the Mapping Engine handles an end useraction affecting a cell in the spreadsheet.

FIG. 19 is an exemplary illustration of the Determine Protectionprocess, a Process diagram showing how the Mapping Engine determines ifa cell is protected.

DESCRIPTION OF EMBODIMENTS

In one aspect of the invention, a mapping engine may permit use of aSpreadsheet as a User Interface to data in a database or database-drivenapplication. The result is to be able to generate and map data from thedatabase or database-driven application into a spreadsheet and have anychanges in the spreadsheet be saved back to the database, which willallow for the use of the calculation engine in the spreadsheet. In doingthis, it is necessary that normal spreadsheet functionality beaugmented. This augmentation will allow for the handling of spreadsheetchanges by the database, and presenting appropriate data choices.

With reference to FIG. 6, a Mapping Engine in accordance withembodiments of the present invention may be used for two processes:creation or initialization of the spreadsheet, and handling of normalspreadsheet operations. After start step 600 and during theinitialization of a spreadsheet in step 610, the spreadsheet isgenerated by calling the Process Template process 650. The result ofthis process will be a spreadsheet with the appropriate data, security,and initialization of Mapping Engine 630 with the data from database 620for the current data in the spreadsheet. When this is done, the resultis passed to the spreadsheet application. While normal spreadsheetoperations 640 are handled, they are processed using Mapping Engine 630at step 660 to ensure that changes are allowed and are saved back todatabase 620. This allows the spreadsheet to be an interface to the dataand not just a means of reporting.

While the result looks like a spreadsheet, certain cells and ranges mustbe mapped between the spreadsheet and the database. To do this, therewill be two types of mappings:

Table Map—maps the fields from records into a range in the spreadsheet.This is seen in FIG. 7—Expense Report Spreadsheet Table Mappings, whererange A5:G13 is mapped to the data from table Day Expense Table 200, andrange J12:L15 is mapped from table Expense Table 100. Fields frommultiple records from these tables are mapped into their respectiveranges.

A Record Map maps fields from a single record into cells in aspreadsheet. This is illustrated in FIG. 8. Cells B2, B3, D2, D3, H14,H15, and H16 are mapped from fields from the record in Expense Table 100with field ID of 5. Cells K5, K6, K7, and K8 are mapped from fields fromthe record in Expense Max Table 300 with the field ID of Department ofIT.

While these Maps are important, so is that the loading of the templatethat contains all information needed to generate or update aspreadsheet. See FIG. 10 and exemplary Template 1000 for the contents ofa template. It may include spreadsheet 1010, a saved version of thespreadsheet in the spreadsheet Application native format. As an example,for Microsoft Excel spreadsheets it can be an XLS file. Options 1020 mayinclude implementation-specific options for the resulting end userinterface in the spreadsheet application. Base Record 1030 is a recordin the database that may be used as a basis for all data andrelationships for the Maps. This may be saved as part of the Template orcan be provided as part of the initialization to dynamically generate a“new” spreadsheet from any record in the base record table. Mappings1040 are a collection of the Table and Record Maps for the Template1000.

FIG. 9 illustrates Mapping Types. For example, Record Map 900 maycontain the name of the table from the database used for the mapping.The record to be mapped is determined by Query 902 which can be a queryfor Base Record 1030 or can use information from Base Record 1030 toquery for a related record, like in FIG. 8. This is an example where theBase Record 1030 is Expense Table 100 with field ID of 5. Data from BaseRecord 1030 is mapped to cells B2, B3, D2, D3, H14, H15, and H16. Whilecells K5, K6, K7, and K8 are mapped from a query from Expense Max Table300 where field Department is the same as the Base Record fieldDepartment, where in this case the Department name is IT. These are twoexamples of a Record Map.

To provide a mapping of each field in a Record Map, a Field Map 905 isdefined for each field to be mapped. This contains the name of the fieldfrom the database, the data type used to handle any mapping or dataconversions, the spreadsheet cell in which to place the field data, aflag to determine if the cell is protected, and a Cell Map. If a fieldis protected, then it is read-only to the end user. A Cell Map 920 is adetailed structure and is dynamic upon the database and spreadsheet. Itcontains any “Display Mappings” that can take the database data andpresent it to the end user. For instance, the database may contain aninteger number between 1 and 50, but the display may be one of the 50states in the US. This mapping would then contain the mapping betweenthe State Names and the Integer Number. It will also contain thereverse, a Database Mapping, if needed to convert and communicate whatthe user inputs into a cell to an appropriate database structure. Thisenables the spreadsheet cells to contain lists and choices from othertables in the database, but still be presentable to the end user.

Table Map 910 may contain the name of the table from the database whichis used for the mapping, a Range for the location on the spreadsheet forthe data to be mapped, Options to specify how to handle the userinterface to the mapped table, a list of Column Maps 915 specifyingwhich fields are mapped and to which column in the Range, and a Query.The records to be mapped is determined by the Query. The Query can useinformation from Base Record 1030 to query for a related record, like inFIG. 7 Expense Report Spreadsheet Table Mappings. This is an examplewhere the Base Record is Expense Table 100 with field ID of 5 and twoTable Maps. The first Table Map Range is A5:G6. This example shows tworecords from Day Expense Table 200 where the field Expense ID is “5”which is the ID of the Base Record. The second Table Map Range isJ12:L15 which shows 4 records from the Expense Table 100 where the fieldName is “Reed Owens” and the field End Date is before the Base Recordfield Begin Date of Jan. 1, 2017.

Column Maps 915 determine which columns from the table are mapped towhich fields. Each Table Map 910 contains zero or more Column Maps 915.Column Map 915 contains the field name from the table, a flag todetermine if the column is protected, into which column in the range thefield should be mapped, and a Cell Map(s) 920.

After Mapping Engine 630 is initialized, it will contain informationabout the Table Definitions of all tables used from the database, andall the Field Definitions used. This allows the spreadsheet to bedatabase-aware of any security, choices, relationships, and data typesfrom the database, and to be able to handle them appropriately duringthe spreadsheet UI functions. Such information concerning TableDefinitions and Field Definitions may be stored in Mapping Engine datastructure 940. When lists, relationships, choices, etc. are retrievedfor a field, the data is cached in the Mapping Engine cache 930 toenable faster response.

At this point, normal spreadsheet operations 640 occur and are processedby Mapping Engine 630. This ensures they are allowed and are processedcorrectly for the mapping to the database/database-driven application.

As shown in FIG. 6, there are two major process points to Mapping Engine630: the initialization using the Process Template process 650, and theProcess User Action process 660.

Process Template process 650 includes a step of loading the Template1000, see FIG. 11, step 1100. Template 1000 can act in two differentmodes. In Generation Mode, Template 1000 may be used to generate a newspreadsheet using Spreadsheet 1010 stored in Template 1000 as a startingpoint. This mode is dictated by Base Record 1030 in Template 1000 onlycontaining the table from the database but no ID. The ID of the exactrecord is passed into Process Template process 650 and is combined withBase Record 1030 table for processing. Continuation Mode is when theTemplate continues using a previously generated spreadsheet andSpreadsheet 1010 stored in Template 1000 is the last saved version ofthe previously generated spreadsheet. This allows for Template 1000 tostore changes to data, formatting, etc. in the spreadsheet that is notmapped.

Options 1020 may contain specific options on how to handle theprocessing and rendering of the spreadsheet.

Mappings 1040 may contain Record Map(s) 900 and Table Map(s) 910. Whileonly one Record Map 900 and one Table Map 910 are shown in FIG. 9,multiple Record Maps 900 and multiple Table Maps 910 can be defined.

After the Record Maps 900 and Table Maps 910 are loaded, Process Mapprocess 1120 is processed for each defined Record Map 900 and Table Map910 loaded per step 1110. The first map processed will be the Record Map900 that specifying the same Table as in Base Record 1030.

With reference to FIGS. 6, 11, and 12, Process Map process 1120 starts astep for retrieving the Table and Field Definitions 1200 from theDatabase 620. This definition is stored in Mapping Engine 630 to enableMapping Engine 630 to conform and transform information to and fromDatabase 620. For the Query from the Database in steps 1220 and 1240,the Query and Base Record from the Map is used to determine the exactdatabase query needed to get the record or records. If the Map type isRecord as determined at step 1210, then step 1240 queries the databasefor the specified record and then Perform Record Mapping process 1250 isperformed; otherwise, step 1220 retrieves the records from the databaseand Perform Table Mapping 1230 is performed.

With reference to FIGS. 9 and 13, a Perform Record Mapping process 1250starts at a step 1300 by going through each Field Map 905 in Record Map900. If the Mapping is not Bidirectional or comes from Database Only, astested at step 1310, then nothing is done for that Field Map 905. Thisis to handle To Database Only Field Maps that have a formula in thespreadsheet cell that maps only its value back to the database and isnever overwritten by data from the database. This enables formulas to beused in the spreadsheet and its calculation engine to update thedatabase with its results.

Otherwise, at step 1320, if Special Mapping and more information fromthe Database is needed, then that information is retrieved from thedatabase and stored in the Mapping Engine 630. For example, in a choicelist, the database value may be a number, but the display value to theuser could be a list of text strings, such as used in a drop-down choicelist. That choice list and those mappings must be loaded from thedatabase for the spreadsheet UI to be usable by the end user.

Then, at step 1330, the cell in the spreadsheet is mapped with the datafrom the database. If it is a Special Mapping cell, the display value isdetermined by going through the Special Mapping routine. SpecialMappings can be a drop-down of a choice list such as mentioned regardingstep 1320, perhaps a related record like a User Name from a User Record,and would require the list of User Records to be selectable by the enduser. A Special Mapping cell can also be something like currency,formatted numbers, dates, etc. This includes anything that requires atranslation from the stored database value and the display valuepresented to the end user.

With reference to FIG. 14, a Perform Table Mapping process 1230 startsby processing each row from the Query from the database at step 1400.The rows from the Query are mapped starting at the first row in theRange specified in Table Map 910. The mapping continues until the finalrow in the Range is reached, so that each row from the Query correspondsto an equivalent row in the range (First Row in Range+Query Rownumber−1). If the current row from the Query is in the Mapped Range,determined at step 1410, then the process continues with Perform RecordMapping process 1250. Otherwise the Row is not valid. Such a row willthen be the First Row in Range+Query Row number−1>Last Row in Range. Ifthe Row is not valid, then a check with the Options from Table Map 910is performed at step 1420 to see if the Options allow for the tablerange to be expanded as needed for queries. If not allowed, then thisTable Mapping is complete, and processing on the Table Map is stopped atstep 1430.

To accommodate a new row, Add New Row To Table Map process 1440, furtherdetailed with reference to FIG. 15, is processed.

Perform Record Mapping process 1250, as further detailed in FIG. 13handles the mappings. Field Map 905 is generated by using Field, Type,and Protected selections from Column Map 915. The cell is determined bya mapped row of the query row in the Range. The column is determined byFirst Column in Range+Column Map index−1. This row:column will be usedas the Cell in Field Map 905.

With reference to FIG. 15, illustrating Add New Row to Table Map process1440, a new row may be added to the Range in a Table Map 910. At firststep 1500, the entire row that is the last row in the Range is copied.For each cell, the data, type, and formatting are copied.

At step 1510, the spreadsheet insert row functionality is used to inserta new row immediately before the last row in the Range. This will causeall the rows after that row to increment by one, and a new “blank” rowwill be inserted. The spreadsheet formulas will be updated by thespreadsheet application to change any references to rows that werechanged to their new value.

For each column in the new row, at step 1520, the column/cell is checkedat step 1530 to see if it is mapped, i.e., it is in a Range in any TableMap or is in a Field Map in any Record Maps. If it is not, then thecorresponding cell from the copy made at step 1500 is copied into thenew cell at step 1540. Any formula and all formatting are copied. Theprocess then continues at step 1520.

At step 1550, the cell is checked to see if it is mapped as a Field Map905 in Record Map 900. If it is, the Field Map 905 Cell location isupdated at step 1560 to reflect the new location of the field byincrementing its row by 1. Then the process continues at step 1540.

If step 1550 determines that it is not a Field Map 905, then the cell isin a Table Map 910. At step 1570, a check is made to determine if thisAdd New Row to Table Map process 1440 is encountering this Table Map forthe first time. If so, at step 1580, the Range in the Table Map 910 isupdated, with the Range Row incremented by 1.

After step 1580 or if the Table Map 910 has been encountered before, theformatting from the corresponding cell in the copy from step 1500 iscopied into the new cell at step 1590. The process then continues atstep 1520.

Referring to FIG. 16, a Process User Action process 660 handles theevents from the spreadsheet that a user invokes through the spreadsheetuser interface. Process 660 starts at step 1600 when the user performsan action on a cell in the spreadsheet. The action is interrogated atstep 1610 to determine if the action causes the cell to be changed. Ifno change is to occur, then the cell is checked at step 1620 to see ifit is in any Map Table Range. If it is not, then the action is passed atstep 1625 to the standard spreadsheet actions as it will not affect theMapping Engine 630. If the cell is in a Map Table Range, then ProcessTable Action 1630, detailed in FIG. 17, is performed.

If the Cell is being changed as determined at step 1610, the DetermineProtection process 1640, further detailed in FIG. 19, is used to see ifthe cell is protected. If it is protected, the change is denied at step1650.

If the Cell is not protected as determined at step 1640, then all Tableand Record Maps are checked to see if the Cell is in any of the Maps atstep 1660. If not, the Cell is not part of any Map and the spreadsheetstandard edit/change is then processed at step 1670.

Otherwise, at step 1680 it is determined if the Cell is in a TableRange. If not, then the Process Cell Action process 1690, furtherdetailed in FIG. 18, is performed. Otherwise, the Process Table Actionprocess 1630 is performed.

With reference to FIG. 17, Process Table Action process 1630 starts atstep 1700 by checking to see if the action is to add a row to the TableMap 910 Range. If a row is to be added, then Add New Row to Table Mapprocess 1440 is invoked.

If the action is not to add a row, then at step 1720, the action ischecked to see if it is to delete a row. If a row is to be deleted, thenthe row in which the Cell Action occurred is used to determine whatrecord from the Table Map 910 Query is to be deleted. That record isdeleted or is traced to be deleted later at step 1730. The record isalso removed from the records stored in the Mapping Engine to reflectthe Table Map on the spreadsheet with the deleted row. The table isrefreshed from Table Map 910 at step 1740. This will cause the removalof the deleted record on the spreadsheet, and will adjust the Table Map910 appropriately with new data or blank rows depending upon the optionsfor Table Map 910 (see FIG. 14, Perform Table Mapping process 1230).

If the determination at step 1720 is not to delete a row, the action ischecked at step 1750 to see if it will cause an update to the cell. Ifit will not, then the action is denied at step 1760. Otherwise the rowin which that Cell Action occurred is used to determine what record fromthe Table Map Query is being updated at step 1770.

If the row is not currently being mapped to a record (i.e., the numberof rows from the Table Map Query contains fewer records than the numberof rows in the Table Map Range and one of the unmapped rows is where theCell Action occurred), then either a record is inserted into thedatabase or this record is marked as existing and will need to beinserted later. When the record is inserted, the Table Map Query is usedto determine the appropriate field values to maintain the properrelationship.

If the record exists as determined at step 1770 or after step 1780, theProcess Cell Action process 1690 is processed (see FIG. 18).

With reference to FIG. 18, Process Cell Action process 1690 starts atstep 1800 by checking whether or not Database 620 allows the user tochange the field/record correlated to the record/field from Table Map910. The permissions from the initialization of Table Map 910 (see FIG.12—Process Map process 1120 step 1200) are used for this determination.

If the database doesn't allow the user to make the change, then theaction is denied at step 1810. Otherwise, at step 1820, the Mapping ischecked to determine if this Cell requires a special mapping. If itdoes, then the special mapping is performed at step 1830. Otherwise, thestandard spreadsheet functionality is used to update the cell at step1840.

Once the cell has the new value, that value is used at step 1850 totranslate the value back to what the database requires. This step can besimple as in text strings requiring no translation, or as complex asneeding to provide a record ID to a related table to store as areference in related tables. This step is very dependent upon thedatabase being used and the types of data formats to support.

When this step is completed, the database is either updated with thetranslated value or it is tracked as needing to be saved later at step1860.

With reference to FIG. 19, Determine Protection process 1640 for thecell in question starts at step 1900 by looking in the Mapping Enginedata structure, see FIG. 9, which contains a list of all protectedranges. If the cell is in any of these ranges as determined at step1910, then it is protected and the process returns the responseprotected at step 1920.

If the cell is not protected, it is then checked to see if it is in anyRecord or Table Map at step 1930. If it is not mapped, then the processreturns ‘Unprotected’ at step 1970. Otherwise, as shown above, the Mapis used to check the cell's protection status at step 1940. If the cellis in a Field Map, the Map has a property to determine if that field isprotected. If it is in a Table Map, the field's Column Map Protectedproperty is used to determine whether or not it is protected. If themapping is protected as determined at step 1950, then the processreturns protected at step 1920.

Otherwise, at step 1960, the database is checked as to whether or notthe user is allowed to change the field/record correlated to therecord/field from the Table Map. The permissions from the initializationof the Table Map (see FIG. 12—Process Map process 1120 step 1200) areused for this determination. If the database doesn't allow the change,then the process returns protected at step 1920, otherwise the processreturns unprotected at step 1970.

Summary of Exemplary Embodiments

Applications provide the End User presentation of Data from Databases invarious manners. Two such methods are, one, to show a single Record in aForm View or, two, to show a list of Records in a List View. Acombination of both can be used where the Form View contains one or moreList Views associated with the Record being viewed. While the data isbeing viewed, changes to the data may be made by the user changing theunderlining Data or even adding or removing new Data in the List Views.

Spreadsheets have been used to help manage Data. This has typicallymeant that a Query for Data is mapped into a Spreadsheet. The Data fromthe Database is retrieved and put into a region in the Spreadsheet.Additional controls may be present to ‘refresh’ the data with currentdata from the Database or even update the Database Data with any changesin the Spreadsheet.

The result of End User Interface for Database Data with Spreadsheets isto combine these previous methods into a new method to create a new UserInterface to Database Data. This is done by using a Spreadsheet as theUI, and by having all the Data presented to the End User be mapped intothe Spreadsheet in a repeatable manner. As in the combination of theForm View and List View, a Record serves as the basis of the Data to bepresented and the Lists from the List Views are mapped into ManagedRegions in the Spreadsheet. With this new UI and its required mappings,a new means to create Applications is now possible. An existingSpreadsheet can be used to create a new Application. The Spreadsheetmappings are created. These mappings are then used to create the Tablesin the Database to store the required data and relationships. The EndUser UI is now complete, because the Spreadsheet is now the UI for theEnd User using the Mapping Engine to Process User Action.

Therefore, the following is claimed:
 1. In a computer system, a methodfor using a spreadsheet as a user interface to a database operablyconnected to the computer system, the computer system having one or moreprocessors and storage, the method carried out through execution ofprogram instructions executed by the one or more processors, the methodcomprising: instantiating a mapping engine, the mapping engine operableto retrieve, manipulate, and store data in the database, in thespreadsheet, and in a plurality of data structures of types including:cell maps, a cell map being a type of data structure adapted tobidirectional mapping or translating of data values inputted anddisplayed via the spreadsheet to data values stored and retrieved fromthe database, column maps, a column map being a type of data structureassociating a database table field with a spreadsheet range column andoptionally having one or more cell maps, table maps, a table map being atype of data structure associating a query on a database table with aspreadsheet range and optionally having one or more column maps, fieldmaps, a field map being a type of data structure associating a databasetable field with a spreadsheet cell and optionally having one or morecell maps, and record maps, a record map being a type of data structureidentifying a database table and having a query on the database table,and optionally having one or more field maps, instantiating the mappingengine including: retrieving a template from storage or the database,the template having the spreadsheet, one or more table maps, and one ormore record maps of which one is a base record map; iterating, over thetemplate's one or more table maps and one or more record maps beginningwith the base record map, the following steps: if the iteration is for aone of said one or more record maps: using the query associated withsaid one record map to retrieve a record from the database and iteratingover each field map associated with said one record map to update thespreadsheet cell associated with the iterated field map with the datafrom the retrieved record stored at the field associated with theiterated field map, applying the mapping or translation designated inthe cell map associated with the iterated field map, if any, if theiteration is for a one of said one or more table maps: using the queryassociated with said one table map to retrieve one or more records fromthe database and iterating over each retrieved record as follows:iterating over each column map associated with said one table map toupdate a spreadsheet cell at R:C in the spreadsheet range associatedwith said one table map with the data from the iterated retrieved recordstored at the field associated with the iterated column map, applyingthe mapping or translation designated in the cell map associated withthe iterated column map, if any, where C is the column associated withthe iterated column map and R is the ordinal number of the currentretrieved record iteration for said one table map; installing calls tothe mapping engine into the spreadsheet, whereby when the user attemptsan action on the spreadsheet, the mapping engine is called; presentingthe spreadsheet to the user; when the user takes an action affecting acell in the spreadsheet, invoking the mapping engine to take thefollowing steps: determining whether the cell is associated with anytable map or record map in the template and, if not, then permitting theaction; determining whether the cell is in a range associated with anytable map in the template and the action is to change the value of thecell and, if so, then: permitting the action affecting the cell,determining whether any record for the cell exists in the database tableassociated by the associated table map and inserting a record if norecord exists, and storing the cell value resulting after the action inthe database table after first applying the mapping or translatingdesignated in a cell map associated with the cell, if any; determiningwhether the cell is in a range associated with any table map in thetemplate and the action is to add a row and, if so, performing theaction by: making a copy of the spreadsheet row containing the last rowin the associated range, causing the spreadsheet to insert a new rowabove the last row in the associated range, iterating over each cell inthe new row by copying, if the iterated cell is not in any table map orrecord map, any format and formula of the cell in the same column in thecopy of the spreadsheet row to the iterated cell, iterating over eachfield map associated with one or more cells in the new row, updating theiterated field map with the new cell location, and iterating over eachtable map associated with one or more cells in the new row, copying anyformat from the same column in the copy of the spreadsheet rowcorrespondingly to each of the one or more cells, and if necessary,updating the iterated table map with new row size information;determining whether the cell is in a range associated with any table mapin the template and the action is to delete a row and, if so, thendeleting the corresponding record from the database and refreshing theassociated table map to reflect the deletions; and determining whetherthe cell is associated with any table map or record map in the templateand not in a range associated with any table map in the template and, ifso, then permitting the action affecting the cell and storing the cellvalue resulting after the action in the database after first applyingthe mapping or translating designated in a cell map associated with thecell, if any.
 2. The method of claim 1 wherein invoking the mappingengine when the user takes an action affecting a cell in the spreadsheetfurther comprises the steps of: determining whether the cell isprotected, and denying the action if the cell is determined to beprotected.
 3. The method of claim 2 wherein the field map and column mapdata structures each further comprise a protection indication flag andthe step of determining whether the cell is protected includes thefollowing steps: determining that the cell is protected if the cell isassociated with a data structure of types field map or column map forwhich the protection indication flag indicates that the cell isprotected, and determining that the cell is protected if it isdetermined that the database does not permit the user to make changes tothe database.
 4. The method of claim 3 wherein determining that thedatabase does not permit the user to make changes to the databaseincludes determining that the database does not permit the user to makechanges to the field or record associated with the cell by theassociated field map or column map.
 5. The method of claim 1 whereinusing the query associated with the iterated record map or the iteratedtable map includes dynamically modifying the query with one or morevalues obtained via a reference.
 6. The method of claim 5 wherein theone or more values obtained via a reference include one or more valuesobtained using a base record reference, a field map reference, or aspreadsheet cell reference.